IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CleanupAnimalBoardReviews]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CleanupAnimalBoardReviews]
GO
/****** Object:  StoredProcedure [dbo].[CleanupAnimalBoardReviews]    Script Date: 10/31/2008 11:49:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Irina Kozlova
-- Create date: 	10/31/2008
-- Description:		Deletes records for Submissions and BoardReview statuses in Insight  
-- 			that were deleted in Res; to be called by Animal feed
-- =============================================
CREATE PROCEDURE dbo.CleanupAnimalBoardReviews

AS
BEGIN

	SET NOCOUNT ON;

	BEGIN TRANSACTION CleanupSubmissions

	--First delete Board Review records for deleted submissions
	SELECT PBR.ID 
	INTO #tempDeletedSubmissionsReviews
	FROM dbo.ProtocolBoardReview PBR	
	WHERE PBR.ProtocolSubmissionId IN 
	(SELECT ps.Id
	FROM ProtocolSubmission PS 
	WHERE PS.ID NOT IN (SELECT ProtocolSubmissionId FROM Stg_AnimalProtocolSubmission
	WHERE ProtocolSubmissionId IS NOT NULL)
	AND PS.ModuleType = 'A')

	DELETE FROM dbo.PendingApplications_Fct
		WHERE BoardReviewId IN (SELECT ID FROM #tempDeletedSubmissionsReviews)

	DELETE FROM dbo.protocolboardreview
		WHERE ID IN (SELECT ID FROM #tempDeletedSubmissionsReviews)
	
	IF (@@ERROR <> 0) 
    		BEGIN
        		RAISERROR ('CleanupAnimalBoardReviews: Failed to delete from ProtocolBoardReview', 16, 1, @@ERROR)
			ROLLBACK TRANSACTION CleanupSubmissions
        		RETURN
   		END

	--delete submissions that were deleted in Rex
	SELECT ps.Id
	INTO #tempDeletedSubmissions
	FROM dbo.ProtocolSubmission PS 
	WHERE PS.ID NOT IN (SELECT ProtocolSubmissionId FROM Stg_AnimalProtocolSubmission
	WHERE ProtocolSubmissionId IS NOT NULL)
	AND PS.ModuleType = 'A'

	DELETE FROM dbo.ProtocolSubmission
		WHERE ID IN (SELECT ID FROM #tempDeletedSubmissions)

	IF (@@ERROR <> 0) 
    		BEGIN
        		RAISERROR ('CleanupAnimalBoardReviews: Failed to delete from ProtocolSubmission', 16, 1, @@ERROR)
			ROLLBACK TRANSACTION CleanupSubmissions
        		RETURN
   		END

	COMMIT TRANSACTION CleanupSubmissions
	
	-- delete the rest of Board Review records that are not in Rex
	BEGIN TRANSACTION CleanupBoardReviews

	SELECT PBR.ID 
	INTO #tempDeletedReviews
	FROM dbo.ProtocolBoardReview PBR
	WHERE PBR.Id NOT IN (SELECT ProtocolBoardReviewId FROM Stg_AnimalProtocolBoardReview
		WHERE ProtocolBoardReviewId IS NOT NULL)
		AND IsAnimalBR = 1	

	DELETE FROM dbo.PendingApplications_Fct
		WHERE BoardReviewId IN (SELECT ID FROM #tempDeletedReviews)

	DELETE FROM dbo.protocolboardreview
		WHERE ID IN (SELECT ID FROM #tempDeletedReviews)

	IF (@@ERROR <> 0) 
    		BEGIN
        		RAISERROR ('CleanupAnimalBoardReviews: Failed to delete from ProtocolBoardReview', 16, 1, @@ERROR)
			ROLLBACK TRANSACTION CleanupBoardReviews
        		RETURN
   		END

	COMMIT TRANSACTION CleanupBoardReviews

	SET NOCOUNT OFF;
END

GO
